﻿#include "odbcutil.h"

OdbcUtil::OdbcUtil()
{
}
OdbcUtil::~OdbcUtil()
{
    closeDatabase();
}

void OdbcUtil::initConnectionType(SqlType type){
    db = QSqlDatabase::addDatabase(getSqlType(type));
}
void OdbcUtil::setDataBaseName(const QString &dataBaseName){
    db.setDatabaseName(dataBaseName);
}
void OdbcUtil::setHostName(const QString &hostName){
    db.setHostName(hostName);
}
void OdbcUtil::setPassword(const QString &password){
    db.setPassword(password);
}
void OdbcUtil::setUserName(const QString &userName){
    db.setUserName(userName);
}
void OdbcUtil::setPort(const int &port){
    db.setPort(port);
}
void OdbcUtil::setConnectOptions(const QString &option){
    db.setConnectOptions(option);
}
void OdbcUtil::setNumericalPrecisionPolicy(QSql::NumericalPrecisionPolicy precision){
    db.setNumericalPrecisionPolicy(precision);
}

bool OdbcUtil::openDatabase()
{
    if (!db.open()) {
        qDebug() << "Error: Could not open database";
        return false;
    }
    else{
        qDebug() << "SQLite connect success!";
    }
    return true;
}

bool OdbcUtil::closeDatabase()
{
    if (db.isOpen()) {
        db.close();
    }
    return true;
}


bool OdbcUtil::executeQuery(const QString &query)
{
    QSqlQuery sqlQuery;
    if (!sqlQuery.prepare(query)) {
        qWarning() << "Error preparing query:" << sqlQuery.lastError().text();
        return false;
    }

    if (!sqlQuery.exec()) {
        qDebug() <<"----------------SQL ERROR--------------------";
        qDebug() <<"query:"<<query;
        qDebug() << "Error executing query:" << sqlQuery.lastError().text();
        qDebug() <<"---------------------------------------------";
        return false;
    }
    return true;
}

bool OdbcUtil::executeQuery(const QString &query,QVariantMap &results){
    QSqlQuery sqlQuery;
    if (!sqlQuery.prepare(query)) {
        qWarning() << "Error preparing query:" << sqlQuery.lastError().text();
        return false;
    }
    if (!sqlQuery.exec()) {
        qDebug() <<"----------------SQL ERROR--------------------";
        qDebug() <<"query:"<<query;
        qDebug() << "Error executing query:" << sqlQuery.lastError().text();
        qDebug() <<"---------------------------------------------";
        return false;
    }
    if (sqlQuery.next()) {
        const QSqlRecord &record = sqlQuery.record();
        for (int i = 0; i < record.count(); ++i) {
            QString fieldName = record.fieldName(i);
            results[fieldName] = record.value(i);
        }
    }
    return true;
}

bool OdbcUtil::executeQuery(const QString &query,QList<QVariantMap> &results)
{
    QSqlQuery sqlQuery(query);
    if (!sqlQuery.exec()) {
        qDebug() <<"----------------SQL ERROR--------------------";
        qDebug() <<"query:"<<query;
        qDebug() << "Error executing query:" << sqlQuery.lastError().text();
        qDebug() <<"---------------------------------------------";
        return false;
    }
    while (sqlQuery.next()) {
        QVariantMap row;
        const QSqlRecord &record = sqlQuery.record();
        for (int i = 0; i < record.count(); ++i) {
            row[record.fieldName(i)] = sqlQuery.value(i);
        }
        results.append(row);
    }
    return true;
}

bool OdbcUtil::executeQueryBind(const QString &query,const QVariantMap &results)
{
    QSqlQuery sqlQuery;
    if (!sqlQuery.prepare(query)) {
        qWarning() << "Error preparing query:" << sqlQuery.lastError().text();
        return false;
    }

    for (const QString &key : results.keys()) {
        sqlQuery.bindValue(QString(":%1").arg(key), results.value(key));
    }

    if (!sqlQuery.exec()) {
        qDebug() <<"----------------SQL ERROR--------------------";
        qDebug() <<"query:"<<query;
        qDebug() << "Error executing query:" << sqlQuery.lastError().text();
        qDebug() <<"---------------------------------------------";
        return false;
    }
    return true;
}

bool OdbcUtil::isEmpty(const std::initializer_list<QString>& strings) {
    for (const QString& str : strings) {
        if (str.isEmpty()) {
            qDebug() << __FILE__ << __LINE__ << "Found an empty string in" << __FUNCTION__;
            return true;
        }
    }
    return false;
}

QList<QVariantMap> OdbcUtil::selectAll(const QString &tableName)
{
    if(isEmpty({tableName})){
        qWarning()<<"Parameter has empty value";
        return QList<QVariantMap>();
    }

    QString query = QString("SELECT * FROM %1").arg(tableName);

    QList<QVariantMap> results;
    if(executeQuery(query,results)){
        return results;
    }
    else
    {
        return QList<QVariantMap>();
    }
}

QList<QVariantMap> OdbcUtil::selectAll(const QString &tableName,const QString &whereClause)
{
    if(isEmpty({tableName,whereClause})){
        qWarning()<<"Parameter has empty value";
        return QList<QVariantMap>();
    }
    QString query = QString("SELECT * FROM %1 WHERE %2").arg(tableName).arg(whereClause);

    QList<QVariantMap> results;
    if(executeQuery(query,results)){
        return results;
    }
    else
    {
        return QList<QVariantMap>();
    }
}

QList<QVariantMap> OdbcUtil::selectAll(const QStringList &fieldNames,const QString &tableName,const QString &whereClause)
{
    if(fieldNames.isEmpty()||isEmpty({tableName,whereClause})){
        qWarning()<<"Parameter has empty value";
        return QList<QVariantMap>();
    }

    QString fieldNamesStr = fieldNames.join(", ");
    QString query = QString("SELECT %1 FROM %2 WHERE %3").arg(fieldNamesStr).arg(tableName).arg(whereClause);

    QList<QVariantMap> results;
    if(executeQuery(query,results)){
        return results;
    }
    else
    {
        return QList<QVariantMap>();
    }
}

QList<QVariantMap> OdbcUtil::selectAll(const QStringList &fieldNames,const QString &table1,const QString &alias1,const QString &table2,const QString &alias2,const QString &conditionField,const QString &whereClause){
    if(fieldNames.isEmpty()||isEmpty({table1,alias1,table2,alias2,conditionField,whereClause})){
        qWarning()<<"Parameter has empty value";
        return QList<QVariantMap>();
    }

    QString fieldNamesStr = fieldNames.join(", ");
    QString query = QString("SELECT %1 FROM %2 %3 join %4 %5 on %3.%6=%5.%6 WHERE %7").arg(fieldNamesStr).arg(table1).arg(alias1).arg(table2).arg(alias2).arg(conditionField).arg(whereClause);

    QList<QVariantMap> results;
    if(executeQuery(query,results)){
        return results;
    }
    else
    {
        return QList<QVariantMap>();
    }
}

QList<QVariantMap> OdbcUtil::selectAll(const QStringList &fieldNames,const QString &table1,const QString &alias1,const QString &table2,const QString &alias2,const QString &conditionField1,const QString &table3,const QString &alias3,const QString &conditionField2,const QString &whereClause){
    if(fieldNames.isEmpty()||isEmpty({table1,alias1,table2,alias2,conditionField1,table3,alias3,conditionField2,whereClause})){
        qWarning()<<"Parameter has empty value";
        return QList<QVariantMap>();
    }

    QString fieldNamesStr = fieldNames.join(", ");
    QString query = QString("SELECT %1 FROM %2 %3 join %4 %5 on %3.%6=%5.%6 join %7 %8 on %3.%9=%8.%9 WHERE %10").arg(fieldNamesStr).arg(table1).arg(alias1).arg(table2).arg(alias2).arg(conditionField1).arg(table3).arg(alias3).arg(conditionField2).arg(whereClause);

    QList<QVariantMap> results;
    if(executeQuery(query,results)){
        return results;
    }
    else
    {
        return QList<QVariantMap>();
    }
}

bool OdbcUtil::selectOne(const QString &tableName,const QString &whereClause)
{
    if(isEmpty({tableName,whereClause})){
        qWarning()<<"Parameter has empty value";
        return false;
    }

    QString query = QString("SELECT 1 FROM %1 WHERE %2").arg(tableName).arg(whereClause);
    QSqlQuery sqlQuery(query);
    if(sqlQuery.next()){
        return true;
    }
    return false;
}

QVariantMap OdbcUtil::selectOne(const QStringList &fieldNames,const QString &tableName,const QString &whereClause){
    if(fieldNames.isEmpty()||isEmpty({tableName,whereClause})){
        qWarning()<<"Parameter has empty value";
        return QVariantMap();
    }
    QString fieldNamesStr = fieldNames.join(", ");
    QString query = QString("SELECT %1 FROM %2 WHERE %3").arg(fieldNamesStr).arg(tableName).arg(whereClause);

    QVariantMap results;
    if(executeQuery(query,results)){
        return results;
    }
    else
    {
        return QVariantMap();
    }
}

bool OdbcUtil::selectOne(QVariantMap &map,const QString &tableName,const QString &whereClause){
    if(map.isEmpty()||isEmpty({tableName,whereClause})){
        qWarning()<<"Parameter has empty value";
        return false;
    }

    QStringList fieldNames;
    for (const QString &fieldName : map.keys()) {
        fieldNames << fieldName;
    }

    QString fieldNamesStr = fieldNames.join(", ");
    QString query = QString("SELECT %1 FROM %2 WHERE %3").arg(fieldNamesStr).arg(tableName).arg(whereClause);

    if(executeQuery(query,map)){
        return true;
    }
    else
    {
        return false;
    }
}

QVariantMap OdbcUtil::selectOne(const QStringList &fieldNames,const QString &table1,const QString &alias1,const QString &table2,const QString &alias2,const QString &conditionField,const QString &whereClause){
    if(fieldNames.isEmpty()||isEmpty({table1,alias1,table2,alias2,conditionField,whereClause})){
        qWarning()<<"Parameter has empty value";
        return QVariantMap();
    }

    QString fieldNamesStr = fieldNames.join(", ");
    QString query = QString("SELECT %1 FROM %2 %3 join %4 %5 on %3.%6=%5.%6 WHERE %7").arg(fieldNamesStr).arg(table1).arg(alias1).arg(table2).arg(alias2).arg(conditionField).arg(whereClause);

    QVariantMap results;
    if(executeQuery(query,results)){
        return results;
    }
    else
    {
        return QVariantMap();
    }
}

bool OdbcUtil::insertRecord(const QString &tableName, const QVariantMap &record)
{
    if(isEmpty({tableName})||record.isEmpty()){
        qWarning()<<"Parameter has empty value";
        return false;
    }
    QStringList fields;
    QStringList placeholders;

    for (const QString &key : record.keys()) {
        fields << key;
        placeholders << ":" + key;
    }

    QString fieldNames = fields.join(", ");
    QString placeholdersStr = placeholders.join(", ");

    QString query = QString("INSERT INTO %1 (%2) VALUES (%3)").arg(tableName).arg(fieldNames).arg(placeholdersStr);

    return executeQueryBind(query,record);
}

bool OdbcUtil::updateOne(const QString &tableName, const QString &fieldName,const QString &value, const QString &whereClause){
    if(isEmpty({tableName,fieldName,value,whereClause})){
        qWarning()<<"Parameter has empty value";
        return false;
    }

    QString placeholdersStr=QString("%1= :%2").arg(fieldName).arg(fieldName);
    QString query = QString("UPDATE %1 SET %2 WHERE %3").arg(tableName).arg(placeholdersStr).arg(whereClause);

    QSqlQuery sqlQuery;
    if (!sqlQuery.prepare(query)) {
        qWarning() << "Error preparing query:" << sqlQuery.lastError().text();
        return false;
    }

    sqlQuery.bindValue(QString(":%1").arg(fieldName),value);

    if (!sqlQuery.exec()) {
        qDebug() << "Error executing query:" << sqlQuery.lastError().text();
        return false;
    }
    return true;
}
bool OdbcUtil::updateRecord(const QString &tableName, const QVariantMap &record, const QString &whereClause)
{
    if(record.isEmpty()||isEmpty({tableName,whereClause})){
        qWarning()<<"Parameter has empty value";
        return false;
    }

    QStringList placeholders;
    for (const QString &key : record.keys()) {
        placeholders << QString("%1 = %2").arg(key).arg(record.value(key).toString());
    }
    QString placeholdersStr = placeholders.join(", ");

    QString query = QString("UPDATE %1 SET %2 WHERE %3").arg(tableName).arg(placeholdersStr).arg(whereClause);
//        qDebug()<<query;
    return executeQuery(query);
}

bool OdbcUtil::updateRecordBind(const QString &tableName, const QVariantMap &record, const QString &whereClause)
{
    if(record.isEmpty()||isEmpty({tableName,whereClause})){
        qWarning()<<"Parameter has empty value";
        return false;
    }

    QStringList placeholders;
    for (const QString &key : record.keys()) {
        placeholders << QString("%1 = :%2").arg(key).arg(key);
    }
    QString placeholdersStr = placeholders.join(", ");

    QString query = QString("UPDATE %1 SET %2 WHERE %3").arg(tableName).arg(placeholdersStr).arg(whereClause);
    return executeQueryBind(query,record);
}

bool OdbcUtil::deleteRecord(const QString &tableName, const QString &whereClause)
{
    if(isEmpty({tableName,whereClause})){
        qWarning()<<"Parameter has empty value";
        return false;
    }

    QString query = QString("DELETE FROM %1 WHERE %2").arg(tableName).arg(whereClause);

    return executeQuery(query);
}
